--create database PTT;
--go

use pttt;
go

Create table Stations(
	id int not null identity,
	name varchar(50) not null,
	latitude real not null,
	longitude real not null,
	Primary key( id)
);

go

Create table Vehicle(
	id int not null identity,
	vtype varchar(1) not null,
	number int not null,
	cityId int
	Primary key( id)
);

create table Travels(
	Stationnumber int not null,
	timeFromTheLastInSeconds int default 0,
	vehicleId int not null,
	stationId int not null,
	Primary key( Stationnumber , vehicleId, stationId)
);

create table ActualPos(
	id int  identity not null,
	vehicleNumber int not null,
	vtype varchar(1) not null,
	latitude real not null,
	longitude real not null,	
	insertTime datetime,
	userID varchar(50) not null,
	Primary key( id)
);


create table City(
	id int not null identity,
	name varchar(80) not null	
	Primary key( id)
	
);

go

Insert into City(name) Values('Budapest');
Insert Into Vehicle(cityId, vtype, number) values(1, 'V' ,49);
Insert Into Vehicle(cityId, number, vtype) values(1, 6, 'V');

go


--Insert Stations
Insert Into Stations(name, latitude, longitude) Values('Szell Kalman Ter', 47.506943, 19.024803);
Insert Into Stations(name, latitude, longitude) Values('Szena ter', 47.507851, 19.02678);
Insert Into Stations(name, latitude, longitude) Values('Mechwart liget', 47.512036, 19.033027);
Insert Into Stations(name, latitude, longitude) Values('Margit hid, Budai Hidfo', 47.515367, 19.039507);
Insert Into Stations(name, latitude, longitude) Values('Jaszai Mari ter', 47.51338, 19.046833);
Insert Into Stations(name, latitude, longitude) Values('Nyugari palyaudvar', 47.509946, 19.056639);
Insert Into Stations(name, latitude, longitude) Values('Oktogon', 47.505522, 10.063686);
Insert Into Stations(name, latitude, longitude) Values('Kiraly utca', 47.502339, 19.063432);
Insert Into Stations(name, latitude, longitude) Values('Wesselenyi utca', 47.499771, 19.067529);
Insert Into Stations(name, latitude, longitude) Values('Blaha Lujza ter', 47.4963, 19.069615);
Insert Into Stations(name, latitude, longitude) Values('Rakoczi ter', 47.493088, 19.07193);
Insert Into Stations(name, latitude, longitude) Values('Harminckettesek tere', 47.489312, 19.071387);
Insert Into Stations(name, latitude, longitude) Values('Corvin negyed', 47.485786, 19.070152);
Insert Into Stations(name, latitude, longitude) Values('Mester utca', 47.482118, 19.068467); 
Insert Into Stations(name, latitude, longitude) Values('Boraros ter', 47.480354, 19.067024);
Insert Into Stations(name, latitude, longitude) Values('Petofi hid, Budai hidfo', 47.47694, 19.059613);
Insert Into Stations(name, latitude, longitude) Values('Budafoki ut (Karinthy Frigyes ut)', 47.475834, 19.054093);
Insert Into Stations(name, latitude, longitude) Values('Moricz Zsigmond korter', 47.477228, 19.047167);

Insert Into Stations(name, latitude, longitude) Values('Kelenfoldi palyaudvar', 47.465784, 19.021899);
Insert Into Stations(name, latitude, longitude) Values('Szent Gellert Templom', 47.468663, 19.024086);
Insert Into Stations(name, latitude, longitude) Values('Csoka utca', 47.471197, 19.026441);
Insert Into Stations(name, latitude, longitude) Values('Tetenyi ut', 47.4629, 19.033664);
Insert Into Stations(name, latitude, longitude) Values('Kosztolanyi Dezso ter', 47.475595, 19.04148);
Insert Into Stations(name, latitude, longitude) Values('Bertalan Lajos utca', 47.479466, 19.053797);
Insert Into Stations(name, latitude, longitude) Values('Szent Gellert Ter', 47.483923, 19.053556);
Insert Into Stations(name, latitude, longitude) Values('Fovam Ter', 47.486437, 19.057264);
Insert Into Stations(name, latitude, longitude) Values('Kalvin Ter', 47.489392, 19.061306);
Insert Into Stations(name, latitude, longitude) Values('Astoria', 47.494319, 19.059984);
Insert Into Stations(name, latitude, longitude) Values('Deak Ferenc ter', 47.497498, 19.054795);


--Travel for 6
Insert Into Travels(Stationnumber, stationId, vehicleId) Values(0, 1, 2);
Insert Into Travels(Stationnumber, stationId, vehicleId) Values(1, 2, 2);
Insert Into Travels(Stationnumber, stationId, vehicleId) Values(2, 3, 2);
Insert Into Travels(Stationnumber, stationId, vehicleId) Values(3, 4, 2);
Insert Into Travels(Stationnumber, stationId, vehicleId) Values(4, 5, 2);
Insert Into Travels(Stationnumber, stationId, vehicleId) Values(5, 6, 2);
Insert Into Travels(Stationnumber, stationId, vehicleId) Values(6, 7, 2);
Insert Into Travels(Stationnumber, stationId, vehicleId) Values(7, 8, 2);
Insert Into Travels(Stationnumber, stationId, vehicleId) Values(8, 9, 2);
Insert Into Travels(Stationnumber, stationId, vehicleId) Values(9, 10, 2);
Insert Into Travels(Stationnumber, stationId, vehicleId) Values(10, 11, 2);
Insert Into Travels(Stationnumber, stationId, vehicleId) Values(11, 12, 2);
Insert Into Travels(Stationnumber, stationId, vehicleId) Values(12, 13, 2);
Insert Into Travels(Stationnumber, stationId, vehicleId) Values(13, 14, 2);
Insert Into Travels(Stationnumber, stationId, vehicleId) Values(14, 15, 2);
Insert Into Travels(Stationnumber, stationId, vehicleId) Values(15, 16, 2);
Insert Into Travels(Stationnumber, stationId, vehicleId) Values(16, 17, 2);
Insert Into Travels(Stationnumber, stationId, vehicleId) Values(17, 18, 2);


--Travel for 49
Insert Into Travels(Stationnumber, stationId, vehicleId) Values(0, 19, 1);
Insert Into Travels(Stationnumber, stationId, vehicleId) Values(1, 20, 1);
Insert Into Travels(Stationnumber, stationId, vehicleId) Values(2, 21, 1);
Insert Into Travels(Stationnumber, stationId, vehicleId) Values(3, 22, 1);
Insert Into Travels(Stationnumber, stationId, vehicleId) Values(4, 23, 1);
Insert Into Travels(Stationnumber, stationId, vehicleId) Values(5, 18, 1);
Insert Into Travels(Stationnumber, stationId, vehicleId) Values(6, 24, 1);
Insert Into Travels(Stationnumber, stationId, vehicleId) Values(7, 25, 1);
Insert Into Travels(Stationnumber, stationId, vehicleId) Values(8, 26, 1);
Insert Into Travels(Stationnumber, stationId, vehicleId) Values(9, 27, 1);
Insert Into Travels(Stationnumber, stationId, vehicleId) Values(10, 28, 1);
Insert Into Travels(Stationnumber, stationId, vehicleId) Values(11, 29, 1);